/*
 * IBM Corporation.
 * Copyright (c) 2014 All Rights Reserved.
 */

package com.ibm.iisp.dataie.service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.persistence.EntityManager;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.ibm.iisp.common.exception.I18nAppException;
import com.ibm.iisp.dataie.vo.DataExportRequest;


/**
 * 类作用：
 * @author Johnny@cn.ibm.com
 * 使用说明：
 */
public abstract class ExcelDataExporter extends AbstractDataExporter {
	/**
	 * 
	 */
	private int sheetIdx = 0;
	/**
	 * @param dao
	 */
	public ExcelDataExporter(DataExportRequest dataExportReq, EntityManager dao) {
		super(dataExportReq, dao);
	}

	Workbook book;
	Sheet sheet;
	private int dataRowNo;
	private int colNamRowNo = 1;
	File dataFile;

	protected Sheet getExportDataSheet(Workbook book) {
		while (sheetIdx < book.getNumberOfSheets()) {
			Sheet s = book.getSheetAt(sheetIdx++);
			if (DataIEUtils.verifySignature(s, null)) {
				return s;
			}
		}
		return null;
	}

	@Override
	public void doExport() throws I18nAppException {
		while (sheetIdx < book.getNumberOfSheets()) {
			sheet = getExportDataSheet(book);
			if (sheet != null) {
				params = DataIEUtils.parseParams(sheet, this.dataExportReq.getFilterParams());
				@SuppressWarnings("unchecked")
				Map<String, Object> fParams = (Map<String, Object>) params.get(DataIEUtils._FILTER_PARAMS);
				if (fParams != null) {
					dataExportReq.getFilterParams().putAll(fParams);
				}
				dataRowNo = 3;
				if (params != null) {
					if (params.get(DataIEUtils._DATA_ROW_START_PARAM) != null) {
						dataRowNo = (Integer) params.get(DataIEUtils._DATA_ROW_START_PARAM);
						dataRowNo--;
					}
					if (params.get(DataIEUtils._COLUMN_ROW_PARAM) != null) {
						colNamRowNo = (Integer) params.get(DataIEUtils._COLUMN_ROW_PARAM);
						colNamRowNo--;
					}
				}
				// 为使一个book能导出多个不同表的数据，按本sheet调整表名
				this.dataExportReq.setTableName(sheet.getRow(0).getCell(0).getStringCellValue());
				int startRowNo = dataRowNo;
				super.doExport();
				Cell cell_1C = sheet.getRow(0).getCell(2);
				if (cell_1C == null) {
					cell_1C = sheet.getRow(0).createCell(2);
				}
				if (fParams == null) {
					fParams = new HashMap<>();
				}
				fParams.put("DownloadAt", new Date());
				fParams.put("DownloadRows", String.valueOf(dataRowNo - startRowNo));
				cell_1C.setCellValue(fParams.toString());
				log.debug("Data export finished.");
				break;
			}
		}
	}

	@Override
	protected String getExportCols() {
		StringBuilder sql = new StringBuilder(1000);
		Row row1 = sheet.getRow(colNamRowNo);
		for (int colCount = 0; colCount < row1.getLastCellNum(); colCount++) {
			Cell cell = row1.getCell(colCount);
			String colName = cell == null ? null : cell.getStringCellValue();
			if (colName == null || colName.length() == 0) {
				break;
			}
			if (colCount > 0) {
				sql.append(",");
			}
			sql.append(colName);
		}
		return sql.toString();
	}

	/* (non-Javadoc)
	 * @see com.ibm.iisp.dataie.service.AbstractDataExporter#exportRow(java.sql.ResultSet)
	 */
	@Override
	protected void exportRow(ResultSet rs, int colCount) throws SQLException {
		Row row = sheet.getRow(dataRowNo);
		if (row == null) {
			row = sheet.createRow(dataRowNo);
		}
		for (int i = 0; i < colCount; i++) {
			Object val = rs.getObject(i + 1);
			if (val != null) {
				Cell cell = row.getCell(i);
				if (cell == null) {
					cell = row.createCell(i);
				}
				if (val instanceof Number) {
					cell.setCellValue(((Number) val).doubleValue());
				} else if (val instanceof Date) {
					cell.setCellValue((Date) val);
				} else {
					cell.setCellValue(val.toString());
				}
			}

		}
		dataRowNo++;

	}

	/* (non-Javadoc)
	 * @see com.ibm.iisp.dataie.service.AbstractDataExporter#finish()
	 */
	@Override
	public void finish() {
		if (book != null) {
			try (OutputStream os = new FileOutputStream(dataFile)) {
				book.write(os);
				book.close();
			} catch (Throwable e) {
				log.error("Error to write excel file!", e);
			}
		}

	}


	@Override
	protected void applyFilter(StringBuilder sql) {
		String oldf = null;
		oldf = dataExportReq.getFilter();
		super.applyFilter(sql);
		// 恢复成原始值，以便下次再替换应用
		if (oldf != null) {
			dataExportReq.setFilter(oldf);
		}
	}

}
